package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAsset;
import org.compiere.model.MAssetTransaction;
import org.compiere.model.MAssetTransactionLine;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInvoice;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRDepartment;
import org.eevolution.model.MHRJob;

import extend.org.compiere.utils.Util;

public class A_Asset_Form_A1 extends SvrProcess {
	
	/*Current context*/
	private Properties m_ctx;
	
	private ProcessInfo pi;
	
	Integer p_C_BPartner_ID, p_Record_ID, p_Manager_ID, p_memberComm1, p_memberComm2, p_memberComm3;
	
	Timestamp p_DateApproval, p_DateReceipt;
	
	String ResultPath;
	
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null) {
				;
			} else if (name.equals("DocumentNo")) {
				p_Record_ID = para[i].getParameterAsInt();
			} else if (name.equals("C_BPartner_ID")) {
				p_C_BPartner_ID = para[i].getParameterAsInt();
			} else if (name.equals("DateApproval")) {
				p_DateApproval = (Timestamp) para[i].getParameter();
			} else if (name.equals("Manager_ID")) {
				p_Manager_ID = para[i].getParameterAsInt();
			} else if (name.equals("DateReceipt")) {
				p_DateReceipt = (Timestamp) para[i].getParameter();
			} else if (name.equals("memberComm1")) {
				p_memberComm1 = para[i].getParameterAsInt();
			} else if (name.equals("memberComm2")) {
				p_memberComm2 = para[i].getParameterAsInt();
			} else if (name.equals("memberComm3")) {
				p_memberComm3 = para[i].getParameterAsInt();
			} else 
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_A1_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 9) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate"));
		
		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(3).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);

		//Header
		sheet.getRow(9).getCell(21).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(57).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		//Approval
		sheet.getRow(14).getCell(50).setCellValue((new MBPartner(m_ctx, p_C_BPartner_ID, get_TrxName()).getName()));
		sheet.getRow(16).getCell(45).setCellValue((new SimpleDateFormat("dd").format(p_DateApproval)));
		sheet.getRow(16).getCell(48).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateApproval)));
		sheet.getRow(16).getCell(54).setCellValue((new SimpleDateFormat("yyyy").format(p_DateApproval)));
		//Document's Static data
		MAssetTransaction transact = new MAssetTransaction(m_ctx, p_Record_ID, get_TrxName());
		//Document
		sheet.getRow(19).getCell(39).setCellValue(transact.getDocumentNo());
		sheet.getRow(19).getCell(47).setCellValue(transact.getDateAcct());
		//Invoice
		MInvoice invoice = new MInvoice(m_ctx, transact.getC_Invoice_ID(), get_TrxName());
		sheet.getRow(24).getCell(20).setCellValue(invoice.getDocumentNo());
		sheet.getRow(24).getCell(27).setCellValue((new SimpleDateFormat("dd").format(invoice.getDateAcct())));
		sheet.getRow(24).getCell(30).setCellValue((new SimpleDateFormat("MMMMM").format(invoice.getDateAcct())));
		sheet.getRow(24).getCell(36).setCellValue((new SimpleDateFormat("yyyy").format(invoice.getDateAcct())));
		//Document Material
		
		//Sender
		sheet.getRow(27).getCell(6).setCellValue((new MBPartner(m_ctx, invoice.getC_BPartner_ID(), get_TrxName()).getName()));
		//Supplier
		sheet.getRow(29).getCell(5).setCellValue((new MBPartner(m_ctx, invoice.getC_BPartner_ID(), get_TrxName()).getName()));
		//Recipient
		sheet.getRow(29).getCell(39).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		//Dispatch date
		
		//Contract 
		sheet.getRow(31).getCell(39).setCellValue(invoice.getCMS_Contract().getCMS_Contract_ContractNum());
		sheet.getRow(31).getCell(46).setCellValue((new SimpleDateFormat("dd").format(invoice.getCMS_Contract().getCMS_Contract_CreatDate())));
		sheet.getRow(31).getCell(49).setCellValue((new SimpleDateFormat("MMMMM").format(invoice.getCMS_Contract().getCMS_Contract_CreatDate())));
		sheet.getRow(31).getCell(55).setCellValue((new SimpleDateFormat("yyyy").format(invoice.getCMS_Contract().getCMS_Contract_CreatDate())));
		//Department
		MAssetTransactionLine line = (transact.getLines(false).length > 0 ? transact.getLines(false)[0] : null);
		String sqlheader = "SELECT HR_Department_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + line.getA_Asset().getC_BPartnerSR_ID();
		int id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(21).getCell(14).setCellValue((new MHRDepartment(m_ctx, id, get_TrxName()).getName()));
		//Warehouse
		sheet.getRow(60).getCell(14).setCellValue(line.getA_Asset().getM_Locator() == null ? "" : line.getA_Asset().getM_Locator().getM_Warehouse().getName());
		//Supplier
		sheet.getRow(60).getCell(22).setCellValue((new MBPartner(m_ctx, invoice.getC_BPartner_ID(), get_TrxName()).getName()));
		//Corresponding account
		
		//Commissions
		String sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm1;
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(82).getCell(3).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(82).getCell(24).setCellValue((new MBPartner(m_ctx, p_memberComm1, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm2;
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(85).getCell(3).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(85).getCell(24).setCellValue((new MBPartner(m_ctx, p_memberComm2, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm3;
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(88).getCell(3).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(88).getCell(24).setCellValue((new MBPartner(m_ctx, p_memberComm3, get_TrxName()).getName()));
		
		sheet.getRow(94).getCell(19).setCellValue((new MBPartner(m_ctx, p_Manager_ID, get_TrxName()).getName()));
		
		sheet.getRow(92).getCell(15).setCellValue((new SimpleDateFormat("dd").format(p_DateReceipt)));
		sheet.getRow(92).getCell(18).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateReceipt)));
		sheet.getRow(92).getCell(24).setCellValue((new SimpleDateFormat("yyyy").format(p_DateReceipt)));
		
		//Table
		int row = 37, row2 = 0, shift = 62;
		
		for (MAssetTransactionLine tLine: transact.getLines(false)) {
			MAsset asset = new MAsset(m_ctx, tLine.getA_Asset_ID(), get_TrxName());
			
			//Shifting and Merging
			sheet.shiftRows(row, row + (++shift), 1);
			sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 6, 10));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 11, 15));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 16, 35));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 36, 40));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 41, 45));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 46, 53));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 54, 64));
			
			R = sheet.createRow(row);
			for(int i = 0; i <= 64; i++){
				C = R.createCell(i);
				C.setCellStyle(mergeStyle);
			}
			
			sheet.getRow(row).getCell(6).setCellValue(tLine.getQty().doubleValue());
			sheet.getRow(row).getCell(16).setCellValue(asset.getName());
			sheet.getRow(row).getCell(36).setCellValue("шт");
			
			row2 = row + 34;
			
			//Shifting and Merging
			sheet.shiftRows(row2, row2 + 30, 1);
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 0, 6));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 7, 11));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 12, 14));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 15, 17));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 18, 20));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 21, 24));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 25, 27));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 28, 30));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 31, 34));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 35, 38));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 39, 41));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 42, 45));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 46, 48));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 49, 52));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 53, 55));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 56, 59));
			sheet.addMergedRegion(new CellRangeAddress(row2, row2, 60, 64));
			
			R = sheet.createRow(row2);
			for(int i = 0; i <= 64; i++){
				C = R.createCell(i);
				C.setCellStyle(mergeStyle);
			}
			
			sheet.getRow(row2).getCell(0).setCellValue(asset.getName());
			sheet.getRow(row2).getCell(7).setCellValue(asset.getValue());
			sheet.getRow(row2).getCell(12).setCellValue("шт");
			sheet.getRow(row2).getCell(15).setCellValue(tLine.getQty().doubleValue());
			sheet.getRow(row2).getCell(18).setCellValue(asset.getAssetCost(p_DateReceipt).doubleValue());
			sheet.getRow(row2).getCell(21).setCellValue(asset.getAssetCost(p_DateReceipt).doubleValue() * tLine.getQty().doubleValue()); 
			
			row++;
		}
		
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
